package com.zhouzhou.fastjava.excel;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Table;
import com.google.common.collect.TreeBasedTable;
import com.zhouzhou.fastjava.excel.exception.InvalidFormatExcelException;
import com.zhouzhou.fastjava.excel.exception.UnreadableExcelException;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import com.zhouzhou.fastjava.excel.exception.ImportException;

import java.io.IOException;
import java.io.InputStream;
import java.text.MessageFormat;
import java.util.*;
import java.util.Map.Entry;
import java.util.function.Consumer;

abstract class ExcelReader {

    static Map<Integer, String[]> readWithChecking(String originFilename, InputStream is, ColumnQualifier[] columnQualifiers, Consumer<String[]> consumer) throws InvalidFormatExcelException, UnreadableExcelException, ImportException {
        Workbook workbook;
        try {
            workbook = WorkbookFactory.create(is);
        } catch (IllegalArgumentException | InvalidFormatException e) {
            throw new InvalidFormatExcelException(e);
        } catch (IOException e) {
            throw new UnreadableExcelException(e);
        }
        int colNum = columnQualifiers.length;
        Map<Integer, String[]> rows = readRows(workbook, colNum);
        Table<Integer, Integer, String> msgTable = checkImportData(rows, columnQualifiers, consumer);
        if (msgTable != null && !msgTable.isEmpty()) {
            Sheet sheet = workbook.getSheetAt(0);
            insertErrMsg(workbook, sheet, colNum, msgTable);
            throw new ImportException(originFilename, workbook, msgTable);
        }
        return rows;
    }

    static Map<Integer, String[]> readRows(Workbook workbook, int colNum) {
        Map<Integer, String[]> excelContent = new LinkedHashMap<>();
        Sheet sheet = workbook.getSheetAt(0);
        int index = getFirstContentRowNo(sheet, colNum);

        for (int i = index; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (isRowEmpty(row, colNum)) {
                continue;
            }
            String[] rowContent = new String[colNum];
            for (int j = 0; j < colNum; j++) {
                rowContent[j] = getCellValue(row.getCell(j));
            }
            excelContent.put(i, rowContent);
        }
        return excelContent;
    }

    private static void insertErrMsg(Workbook workbook, Sheet sheet, int colCount, Table<Integer, Integer, String> msgTable) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setColor(HSSFColor.RED.index);
        font.setBold(true);
        style.setFont(font);
        Set<Integer> rowNums = msgTable.rowKeySet();
        for (Integer rowNum : rowNums) {
            Map<Integer, String> rows = msgTable.row(rowNum);
            for (Integer colNum : rows.keySet()) {
                Row row = sheet.getRow(rowNum);
                Cell cell = row.createCell(colCount);
                cell.setCellValue(msgTable.get(rowNum, colNum));
                cell.setCellStyle(style);
            }
        }
        sheet.setColumnWidth(colCount, 60 * 256);
    }

    private static Table<Integer, Integer, String> checkImportData(Map<Integer, String[]> rows, ColumnQualifier[] qualifiers, Consumer<String[]> consumer) {
        Table<Integer, Integer, String> errorMsgTable = TreeBasedTable.create();
        if (rows != null && !rows.isEmpty() && qualifiers != null && qualifiers.length > 0) {
            int qualifiersLength = qualifiers.length;

            // column, row, data
            Map<Integer, Map<Integer, String>> uniqueDatas = Maps.newHashMap();

            for (Entry<Integer, String[]> row : rows.entrySet()) {
                Integer rownum = row.getKey();
                String[] vals = row.getValue();
                List<String> errorMsgs = Lists.newArrayList();
                Care:
                for (int i = 0; i < vals.length; i++) {
                    if (i == qualifiersLength) {
                        break;
                    }
                    ColumnQualifier qualifier = qualifiers[i];
                    if (qualifier == null) {
                        continue;
                    }
                    String name = qualifier.getName();
                    String val = vals[i];

                    // 做格式上的Check
                    {
                        String errorMsg = checkColumnDataFormat(qualifier, vals, i);
                        if (StringUtils.isNotBlank(errorMsg)) {
                            errorMsgs.add(name + ":" + errorMsg);
                            continue;
                        }
                    }

                    // unique的check
                    if (qualifier.isUnique() && StringUtils.isNotBlank(val)) {
                        Map<Integer, String> rowDatas = uniqueDatas.computeIfAbsent(i, k -> Maps.newHashMap());
                        for (Entry<Integer, String> entry : rowDatas.entrySet()) {
                            if (entry.getValue().equals(val)) {
                                errorMsgs.add(name + ":" + "与第" + entry.getKey() + "行数据重复");
                                continue Care;
                            }
                        }
                        rowDatas.put(rownum, val);
                    }

                    // 自定义的Check
                    {
                        String errorMsg = checkColumnDataLogic(qualifier, vals, i);
                        if (StringUtils.isNotBlank(errorMsg)) {
                            errorMsgs.add(name + ":" + errorMsg);
                            continue;
                        }
                    }
                }
                if (CollectionUtils.isEmpty(errorMsgs)) {
                    try {
                        consumer.accept(vals);
                    } catch (Exception e) {
                        if (StringUtils.isBlank(e.getMessage())) {
                            e.printStackTrace();
                            errorMsgs.add(e.getClass().getName());
                        } else {
                            errorMsgs.add(e.getMessage());
                        }
                    }
                }
                if (CollectionUtils.isNotEmpty(errorMsgs)) {
                    errorMsgTable.put(rownum, vals.length, StringUtils.join(errorMsgs, ", "));
                }
            }
        }
        return errorMsgTable;
    }

    private static String checkColumnDataFormat(ColumnQualifier qualifier, String[] vals, int columnIndex) {
        List<String> valList = qualifier.getIns();
        String val = vals[columnIndex];
        if (!qualifier.isAllowBlank()) {
            if (StringUtils.isBlank(val)) {
                return "不能为空";
            }
        }
        if (StringUtils.isNotBlank(val)) {
            if (val.length() > qualifier.getMaxLength()) {
                return MessageFormat.format("不能超过{0}个字符", qualifier.getMaxLength());
            }
            if (qualifier.isMustNumber()) {
                if (!NumberUtils.isNumber(val)) {
                    return "必须为数字";
                } else {
                    Float numVal = Float.valueOf(val);
                    Float max = qualifier.getMax();
                    Float min = qualifier.getMin();
                    if (max != null && numVal > max) {
                        return StringUtils.join("不能大于最大值", String.valueOf(max));
                    } else if (min != null && numVal < min) {
                        return StringUtils.join("不能小于最小值", String.valueOf(min));
                    }
                    if (qualifier.isMustPositiveNumber() && val.contains("-")) {
                        return "必须为正整数";
                    }
                    if (qualifier.getScale() == 0 && val.contains(".")) {
                        return "必须整数";
                    } else {
                        String[] values = val.split("\\.");
                        if (values[0].length() > qualifier.getPrecision()) {
                            return MessageFormat.format("整数部分长度不能大于{0}位", qualifier.getPrecision());
                        }
                        if (values.length > 1 && values[1].length() > qualifier.getScale()) {
                            return MessageFormat.format("小数部分长度不能大于{0}位", qualifier.getScale());
                        }
                    }
                }
            }
            if (valList != null) {
                if (!valList.contains(val)) {
                    return MessageFormat.format("的值必须是{0}其中一个", valList);
                }
            }
        }
        return StringUtils.EMPTY;
    }

    private static String checkColumnDataLogic(ColumnQualifier qualifier, String[] vals, int columnIndex) {

        String val = vals[columnIndex];
        if (qualifier != null) {
            Call call = qualifier.getCall();
            if (call != null) {
                try {
                    call.check(val, vals);
                } catch (CellDataIllegalException e) {
                    return e.getMessage();
                }
            }
        }
        return StringUtils.EMPTY;
    }

    @SuppressWarnings("deprecation")
    private static boolean isRowEmpty(Row row, int colNum) {
        if (null == row) {
            return true;
        }
        int lastCellNum = colNum < 0 ? row.getLastCellNum() : colNum;
        for (int c = row.getFirstCellNum(); c < lastCellNum; c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
                return false;
        }
        return true;
    }


    private static int getFirstContentRowNo(Sheet sheet, int colNum) {
        int i = 0;
        int len = sheet.getLastRowNum();
        for (; i <= len; i++) {
            Row row = sheet.getRow(i);
            if (isRowEmpty(row, colNum)) {
                continue;
            }
            Cell cell = null;
            int j = 0;
            for (; j < 10; j++) { // 如果前面10列都没有数据，则认为这是个空行
                cell = row.getCell(j);
                if (cell != null) {
                    break;
                }
            }
            if (cell == null) {
                continue;
            }
            String cellValue = getCellValue(cell);
            if (cellValue != null && !cellValue.startsWith("#")) {
                return i;
            }
        }
        return i + 1;
    }

    @SuppressWarnings("deprecation")
    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        String cellValue;
        int cellType = cell.getCellType();
        if (cellType == HSSFCell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)) {
            Date date = cell.getDateCellValue();
            cellValue = DateFormatUtils.format(date, "yyyy-MM-dd");
        } else if (cellType == Cell.CELL_TYPE_STRING) {
            cellValue = StringUtils.trim(cell.getStringCellValue());
        } else {
            // for get string content
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cellValue = StringUtils.trim(cell.getStringCellValue());
        }
        return cellValue;
    }

    private ExcelReader() {
    }

}
